﻿
Imports MySql.Data.MySqlClient
Imports System.Collections.Generic
Imports System.Data
Imports System.Globalization
Imports System.Text
Imports System.Threading

Public Class cMySql
    Implements IDisposable

    Public Event Connected(Connection As String, Database As String)
    Public Event Disconnected()

    Private _RowCount As Integer
    Public Property RowCount() As Integer
        Get
            Return _RowCount
        End Get
        Set(value As Integer)
            _RowCount = value
        End Set
    End Property

    Private _WorkThread As Thread
    Private _AbortWorkThread As Boolean
    Private _Conn As MySqlConnection
    Private Sub WorkThread()
        Do
            Try
                If IsNothing(_Conn) Then
                    _Conn = New MySqlConnection(_ConnectionString)
                End If
                If _Conn.State = ConnectionState.Closed Then
                    _Conn.Open()
                    RaiseEvent Connected(_host & ":" & _port & "@" & _user, _database)
                End If
                If DateDiff(DateInterval.Minute, Now, _LastUseOfConnection) > 5 Then
                    _Conn.Ping()
                    _LastUseOfConnection = Now
                End If
            Catch ex As Exception
                RaiseEvent Disconnected()
            Finally
                Thread.Sleep(10000)
            End Try
        Loop Until _AbortWorkThread
    End Sub

    Private _host As String
    Private _port As UInt16
    Private _user As String
    Private _password As String
    Private _database As String
    Private _LastUseOfConnection As Date
    Private _ConnectionString As String
    Public Sub New(host As String, port As Integer, user As String, password As String, database As String)
        _host = host : _port = port : _user = user : _password = password : _database = database
        _ConnectionString = "Server=" & _host & ";User Id=" & _user & ";Port=" & _port & ";" & "Password=" & _password & ";Database=" & _database & ";Allow Zero Datetime=True;CharSet=utf8;pooling=false;Default Command Timeout=300;"
        _LastUseOfConnection = Now
        _WorkThread = New Thread(AddressOf WorkThread)
        _WorkThread.Start()
    End Sub

    Public Function ExecuteNonQuery(sql As String, ParamArray args As Object()) As Boolean
        Dim sqlString As New StringBuilder()
        ' benutzt bei fließkomma zahlen immer . und nicht das deutsche ,
        sqlString.AppendFormat(CultureInfo.GetCultureInfo("en-US").NumberFormat, sql)

        Try
            Using sqlCommand As New MySqlCommand(sqlString.ToString(), _Conn)
                Dim mParams = New List(Of MySqlParameter)(args.Length)

                For Each a As Object In args
                    mParams.Add(New MySqlParameter("", a))
                Next

                sqlCommand.Parameters.AddRange(mParams.ToArray())
                sqlCommand.ExecuteNonQuery()
            End Using

            Return True
        Catch ex As MySqlException
            ' Log.Message(LogType.[Error], "{0}", ex.Message)
            Return False
        End Try
    End Function
    Public Sub ExecuteNonQuery(table As String, fields As String, fieldCount As Integer, resultCount As Integer, values As Object(), Optional resultAsIndex As Boolean = True)
        If values.Length > 0 Then
            Dim sqlString As New StringBuilder()

            sqlString.AppendFormat("INSERT INTO {0} ({1}) VALUES ", table, fields)

            For i As Integer = 0 To resultCount - 1
                sqlString.AppendFormat("(", CultureInfo.InvariantCulture)

                For j As Integer = 0 To fieldCount - 1
                    Dim index As Integer = If(resultAsIndex, i, j)

                    If j = fieldCount - 1 Then
                        sqlString.Append(String.Format(CultureInfo.GetCultureInfo("en-US").NumberFormat, "'{0}'", values(index)))
                    Else
                        sqlString.Append(String.Format(CultureInfo.GetCultureInfo("en-US").NumberFormat, "'{0}', ", values(index)))
                    End If
                Next

                If i = resultCount - 1 Then
                    sqlString.AppendFormat(");", CultureInfo.InvariantCulture)
                Else
                    sqlString.AppendFormat("),", CultureInfo.InvariantCulture)
                End If
            Next

            Dim sqlCommand As New MySqlCommand(sqlString.ToString(), _Conn)
            sqlCommand.ExecuteNonQuery()
        End If
    End Sub

    ' Ein Beispiel für den Aufruf:  
    ' DB.World.Select("SELECT * FROM creature_stats WHERE id = ?", id);
    ' DB.Characters.Select("SELECT action, slotId FROM character_creation_actions WHERE race = ? AND class = ? ORDER BY slotId ASC", pChar.Race, pChar.Class);

    Public Function ExecuteReader(sql As String, ParamArray args As Object()) As cMySqlResult
        Dim sqlString As New StringBuilder()
        ' Fix for floating point problems on some languages
        sqlString.AppendFormat(CultureInfo.GetCultureInfo("en-US").NumberFormat, sql)

        Try
            Using sqlCommand = New MySqlCommand(sqlString.ToString(), _Conn)
                Dim mParams = New List(Of MySqlParameter)(args.Length)

                For Each a As Object In args
                    mParams.Add(New MySqlParameter("", a))
                Next

                sqlCommand.Parameters.AddRange(mParams.ToArray())

                Using SqlData = sqlCommand.ExecuteReader(CommandBehavior.[Default])
                    Using retData = New cMySqlResult()
                        retData.Load(SqlData)
                        retData.Count = retData.Rows.Count

                        Return retData
                    End Using
                End Using
            End Using
        Catch ex As MySqlException
            ' Log.Message(LogType.[Error], "{0}", ex.Message)
        End Try

        Return Nothing
    End Function

    Public Shared Function TryConnect(Host As String, Port As UInt16, User As String, Password As String) As String
        Dim ms As MySQL
        Dim ErrorMessage As String = ""
        Try
            ms = New MySQL(Host, Port, User, Password, "")
            Return ""
        Catch ex As Exception
            ErrorMessage = ex.Message
        End Try
        Return ErrorMessage
    End Function
    Public Shared Function TryConnect(Host As String, Port As UInt16, User As String, Password As String, Database As String) As String
        Dim ms As MySQL
        Dim ErrorMessage As String = ""
        Try
            ms = New MySQL(Host, Port, User, Password, Database)
            Return ""
        Catch ex As Exception
            ErrorMessage = ex.Message
        End Try
        Return ErrorMessage
    End Function

    Public Sub Dispose1() Implements IDisposable.Dispose
        _AbortWorkThread = True
        _Conn.Close()
        _ConnectionString = Nothing
    End Sub


End Class
